with tmp_network as (
    select case
               when network_type = 3 then 1 --加盟
               when network_type = 4 then 2 --中心
               when network_type = 5 then 3 --集散
               when network_type = 6 then 4 --网点
               when network_type = 2 then 5 --代理区
               when network_type = 1 then 6 --总部
               else 0
        end            as network_type
         , code
         , name        as network_name     --网点名称
         , agent_code                      --代理区编码
         , agent_name                      --代理区名称
         , fran_code   as franchisee_code  --加盟商编码
         , fran_name   as franchisee_name  --加盟商名称
         , center_code as transcenter_code --转运中心编码
         , center_name as transcenter_name --转运中心名称
    from jms_dim.dim_network_whole_massage
)

insert overwrite table jms_dwd.dwd_tab_barscan_sign_base_hi partition (dt)
select tmp_barscan.billcode              as waybill_no
     , tmp_barscan.delivertime           as signed_time
     , tmp_barscan.receipttype           as signed_type
     , tmp_barscan.deliveruser           as deliver_user
     , tmp_barscan.signuser              as signed_user
     , tmp_barscan.signedstatus          as scan_type
     , tmp_barscan.inputuser             as scan_user
     , tmp_barscan.inputtime             as input_time
     , tmp_barscan.remark                as remark
     , tmp_barscan.inputsite             as signed_site
     , tmp_barscan.deliverfee            as deliver_fee
     , tmp_barscan.freightcollect        as freight_collect
     , tmp_barscan.sendstation           as send_station
     , tmp_barscan.destination           as destination
     , tmp_barscan.realweight            as pic_flag
     , tmp_barscan.cod                   as cod
     , tmp_barscan.fctomonth             as fcto_month
     , tmp_barscan.fccurrency            as fc_currency
     , tmp_barscan.deliverfeecurrency    as deliver_fee_currency
     , tmp_barscan.codcurrency           as cod_currency
     , tmp_barscan.codflag               as cod_flag
     , tmp_barscan.sendsite              as send_site
     , tmp_barscan.destsite              as signed_site_2
     , tmp_barscan.senddate              as send_date
     , tmp_barscan.recepitsigned         as recepit_signed
     , tmp_barscan.siteaccountflag       as site_account_flag
     , tmp_barscan.fcmonthlyaccount      as pistol_id
     , tmp_barscan.transferflag          as transfer_flag
     , tmp_barscan.transfertime          as transfer_time
     , tmp_barscan.transferuser          as transfer_user
     , tmp_barscan.commision             as co_operate_src
     , tmp_barscan.inputtype             as input_type
     , tmp_barscan.modifystatus          as auto_produce_type
     , tmp_barscan.currency              as currency
     , tmp_barscan.collecttype           as payment
     , tmp_barscan.shifts                as shifts
     , tmp_barscan.commisionremark       as commision_remark
     , tmp_barscan.pcs                   as pcs
     , tmp_barscan.chargedweight         as charged_weight
     , tmp_barscan.contractarea          as contract_area
     , tmp_barscan.agentflag             as agent_flag
     , tmp_barscan.scansitecode          as signed_site_code
     , tmp_barscan.uploadtime            as upload_time
     , tmp_barscan.remark3               as remark3
     , tmp_barscan.scanusercode          as scan_user_code
     , tmp_barscan.send_deliver_usercode as send_user_code
     , tmp_barscan.nextstationcode       as next_station_code
     , tmp_barscan.source                as source
     , tmp_barscan.remark5               as signed_cust
     , tmp_barscan.remark6               as remark6
     , tmp_barscan.remark4               as list_code
     , tmp_barscan.scansiteid            as scan_site_id
     , tmp_barscan.nextstationid         as next_station_id
     , tmp_barscan.scanuserid            as scan_user_id
     , tmp_barscan.send_deliver_userid   as send_user_id
     , tmp_network.network_type          as network_type
     , tmp_network.agent_code            as site_agent_code
     , tmp_network.agent_name            as site_agent_name
     , tmp_network.franchisee_code       as site_franchisee_code
     , tmp_network.franchisee_name       as site_franchisee_name
     , tmp_network.transcenter_code      as site_transcenter_code
     , tmp_network.transcenter_name      as site_transcenter_name
     , tmp_network_next.network_type     as pre_network_type
     , case
           when (length(tmp_barscan.billcode) = 13) then 'GB'
           when (length(tmp_barscan.billcode) = 15 and tmp_barscan.billcode like "55%") then 'BS'
           when (length(tmp_barscan.billcode) = 12 and (tmp_barscan.billcode like "40%" or billcode like "42%"))
               then 'BSB'
           when (length(tmp_barscan.billcode) = 15 and tmp_barscan.billcode like "JT%") then 'JT'
           when (length(tmp_barscan.billcode) = 13 and tmp_barscan.billcode like "B%") then 'JTB'
           else "Others"
    end                                  as waybill_source
     , '{{ execution_date | cst_hour }}'    as dt
from jms_ods.tab_barscan_sign_hi tmp_barscan
         left join tmp_network tmp_network on tmp_barscan.scansitecode = tmp_network.code
         left join tmp_network tmp_network_next on tmp_barscan.nextstationcode = tmp_network_next.code
where tmp_barscan.dt = '{{ execution_date | cst_hour }}';
